{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# -*- coding: utf-8 -*-\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from matplotlib import pyplot as plt \n",
    "import seaborn as sns\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_train = pd.read_csv('./data/train.csv')\n",
    "df_test = pd.read_csv('./data/test.csv')\n",
    "df_songs=pd.read_csv('./data/songs.csv')#歌曲元数据信息\n",
    "df_members=pd.read_csv('./data/members.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#定义将isrc转换成年份\n",
    "def isrc_to_year(isrc):\n",
    "    if type(isrc) == str:\n",
    "        if int(isrc[5:7]) > 17:#根据分布，最早为1918\n",
    "            return 1900 + int(isrc[5:7])\n",
    "        else:\n",
    "            return 2000 + int(isrc[5:7])\n",
    "    else:\n",
    "        return np.nan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#对isrc进行转化\n",
    "songs_extra = pd.read_csv('./data/song_extra_info.csv')\n",
    "songs_extra['song_year'] = songs_extra['isrc'].apply(isrc_to_year)\n",
    "songs_extra.drop(['isrc', 'name'], axis = 1, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def count_vals(x):#后面计数的时候又写了一次，有冗余\n",
    "    if type(x) != str:\n",
    "        return 1\n",
    "    else:\n",
    "        return 1 + x.count('|')\n",
    "df_songs['number_of_genres'] = df_songs['genre_ids'].apply(count_vals)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_members['use_days'] = pd.to_datetime(df_members['expiration_date'],format = '%Y%m%d',errors = 'ignore') \\\n",
    "                                - pd.to_datetime(df_members['registration_init_time'],format = '%Y%m%d',errors = 'ignore')\n",
    "df_members['use_days'] = df_members['use_days'].apply(lambda x:x.days)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from datetime import datetime\n",
    "df_members['expiration_date_year'] = pd.to_datetime(df_members['expiration_date'],format = '%Y%m%d',errors = 'ignore').dt.year\n",
    "df_members['expiration_date_month'] = pd.to_datetime(df_members['expiration_date'],format = '%Y%m%d',errors = 'ignore').dt.month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>msno</th>\n",
       "      <th>song_id</th>\n",
       "      <th>source</th>\n",
       "      <th>source_screen_name</th>\n",
       "      <th>source_system_tab</th>\n",
       "      <th>source_type</th>\n",
       "      <th>target</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=</td>\n",
       "      <td>BBzumQNXUHKdEBOB7mAJuzok+IJA1c2Ryg/yzTF6tik=</td>\n",
       "      <td>train</td>\n",
       "      <td>Explore</td>\n",
       "      <td>explore</td>\n",
       "      <td>online-playlist</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=</td>\n",
       "      <td>bhp/MpSNoqoxOIB+/l8WPqu6jldth4DIpCm3ayXnJqM=</td>\n",
       "      <td>train</td>\n",
       "      <td>Local playlist more</td>\n",
       "      <td>my library</td>\n",
       "      <td>local-playlist</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=</td>\n",
       "      <td>JNWfrrC7zNN7BdMpsISKa4Mw+xVJYNnxXh3/Epw7QgY=</td>\n",
       "      <td>train</td>\n",
       "      <td>Local playlist more</td>\n",
       "      <td>my library</td>\n",
       "      <td>local-playlist</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=</td>\n",
       "      <td>2A87tzfnJTSWqD7gIZHisolhe4DMdzkbd6LzO1KHjNs=</td>\n",
       "      <td>train</td>\n",
       "      <td>Local playlist more</td>\n",
       "      <td>my library</td>\n",
       "      <td>local-playlist</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=</td>\n",
       "      <td>3qm6XTZ6MOCU11x8FIVbAGH5l5uMkT3/ZalWG1oo2Gc=</td>\n",
       "      <td>train</td>\n",
       "      <td>Explore</td>\n",
       "      <td>explore</td>\n",
       "      <td>online-playlist</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id                                          msno  \\\n",
       "0 NaN  FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=   \n",
       "1 NaN  Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=   \n",
       "2 NaN  Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=   \n",
       "3 NaN  Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=   \n",
       "4 NaN  FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=   \n",
       "\n",
       "                                        song_id source   source_screen_name  \\\n",
       "0  BBzumQNXUHKdEBOB7mAJuzok+IJA1c2Ryg/yzTF6tik=  train              Explore   \n",
       "1  bhp/MpSNoqoxOIB+/l8WPqu6jldth4DIpCm3ayXnJqM=  train  Local playlist more   \n",
       "2  JNWfrrC7zNN7BdMpsISKa4Mw+xVJYNnxXh3/Epw7QgY=  train  Local playlist more   \n",
       "3  2A87tzfnJTSWqD7gIZHisolhe4DMdzkbd6LzO1KHjNs=  train  Local playlist more   \n",
       "4  3qm6XTZ6MOCU11x8FIVbAGH5l5uMkT3/ZalWG1oo2Gc=  train              Explore   \n",
       "\n",
       "  source_system_tab      source_type  target  \n",
       "0           explore  online-playlist     1.0  \n",
       "1        my library   local-playlist     1.0  \n",
       "2        my library   local-playlist     1.0  \n",
       "3        my library   local-playlist     1.0  \n",
       "4           explore  online-playlist     1.0  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_train['source'] = 'train'\n",
    "df_test['source'] = 'test'\n",
    "data = pd.concat([df_train,df_test],ignore_index = False)\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#data.drop({'id'},axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>msno</th>\n",
       "      <th>song_id</th>\n",
       "      <th>source</th>\n",
       "      <th>source_screen_name</th>\n",
       "      <th>source_system_tab</th>\n",
       "      <th>source_type</th>\n",
       "      <th>target</th>\n",
       "      <th>city</th>\n",
       "      <th>bd</th>\n",
       "      <th>gender</th>\n",
       "      <th>...</th>\n",
       "      <th>expiration_date_year</th>\n",
       "      <th>expiration_date_month</th>\n",
       "      <th>song_length</th>\n",
       "      <th>genre_ids</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>composer</th>\n",
       "      <th>lyricist</th>\n",
       "      <th>language</th>\n",
       "      <th>number_of_genres</th>\n",
       "      <th>song_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=</td>\n",
       "      <td>BBzumQNXUHKdEBOB7mAJuzok+IJA1c2Ryg/yzTF6tik=</td>\n",
       "      <td>train</td>\n",
       "      <td>Explore</td>\n",
       "      <td>explore</td>\n",
       "      <td>online-playlist</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>2017</td>\n",
       "      <td>10</td>\n",
       "      <td>206471.0</td>\n",
       "      <td>359</td>\n",
       "      <td>Bastille</td>\n",
       "      <td>Dan Smith| Mark Crew</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2016.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=</td>\n",
       "      <td>bhp/MpSNoqoxOIB+/l8WPqu6jldth4DIpCm3ayXnJqM=</td>\n",
       "      <td>train</td>\n",
       "      <td>Local playlist more</td>\n",
       "      <td>my library</td>\n",
       "      <td>local-playlist</td>\n",
       "      <td>1.0</td>\n",
       "      <td>13</td>\n",
       "      <td>24</td>\n",
       "      <td>female</td>\n",
       "      <td>...</td>\n",
       "      <td>2017</td>\n",
       "      <td>9</td>\n",
       "      <td>284584.0</td>\n",
       "      <td>1259</td>\n",
       "      <td>Various Artists</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1999.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=</td>\n",
       "      <td>JNWfrrC7zNN7BdMpsISKa4Mw+xVJYNnxXh3/Epw7QgY=</td>\n",
       "      <td>train</td>\n",
       "      <td>Local playlist more</td>\n",
       "      <td>my library</td>\n",
       "      <td>local-playlist</td>\n",
       "      <td>1.0</td>\n",
       "      <td>13</td>\n",
       "      <td>24</td>\n",
       "      <td>female</td>\n",
       "      <td>...</td>\n",
       "      <td>2017</td>\n",
       "      <td>9</td>\n",
       "      <td>225396.0</td>\n",
       "      <td>1259</td>\n",
       "      <td>Nas</td>\n",
       "      <td>N. Jones、W. Adams、J. Lordan、D. Ingle</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2006.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=</td>\n",
       "      <td>2A87tzfnJTSWqD7gIZHisolhe4DMdzkbd6LzO1KHjNs=</td>\n",
       "      <td>train</td>\n",
       "      <td>Local playlist more</td>\n",
       "      <td>my library</td>\n",
       "      <td>local-playlist</td>\n",
       "      <td>1.0</td>\n",
       "      <td>13</td>\n",
       "      <td>24</td>\n",
       "      <td>female</td>\n",
       "      <td>...</td>\n",
       "      <td>2017</td>\n",
       "      <td>9</td>\n",
       "      <td>255512.0</td>\n",
       "      <td>1019</td>\n",
       "      <td>Soundway</td>\n",
       "      <td>Kwadwo Donkoh</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2010.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=</td>\n",
       "      <td>3qm6XTZ6MOCU11x8FIVbAGH5l5uMkT3/ZalWG1oo2Gc=</td>\n",
       "      <td>train</td>\n",
       "      <td>Explore</td>\n",
       "      <td>explore</td>\n",
       "      <td>online-playlist</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>2017</td>\n",
       "      <td>10</td>\n",
       "      <td>187802.0</td>\n",
       "      <td>1011</td>\n",
       "      <td>Brett Young</td>\n",
       "      <td>Brett Young| Kelly Archer| Justin Ebach</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2016.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           msno  \\\n",
       "0  FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=   \n",
       "1  Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=   \n",
       "2  Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=   \n",
       "3  Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=   \n",
       "4  FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=   \n",
       "\n",
       "                                        song_id source   source_screen_name  \\\n",
       "0  BBzumQNXUHKdEBOB7mAJuzok+IJA1c2Ryg/yzTF6tik=  train              Explore   \n",
       "1  bhp/MpSNoqoxOIB+/l8WPqu6jldth4DIpCm3ayXnJqM=  train  Local playlist more   \n",
       "2  JNWfrrC7zNN7BdMpsISKa4Mw+xVJYNnxXh3/Epw7QgY=  train  Local playlist more   \n",
       "3  2A87tzfnJTSWqD7gIZHisolhe4DMdzkbd6LzO1KHjNs=  train  Local playlist more   \n",
       "4  3qm6XTZ6MOCU11x8FIVbAGH5l5uMkT3/ZalWG1oo2Gc=  train              Explore   \n",
       "\n",
       "  source_system_tab      source_type  target  city  bd  gender    ...     \\\n",
       "0           explore  online-playlist     1.0     1   0     NaN    ...      \n",
       "1        my library   local-playlist     1.0    13  24  female    ...      \n",
       "2        my library   local-playlist     1.0    13  24  female    ...      \n",
       "3        my library   local-playlist     1.0    13  24  female    ...      \n",
       "4           explore  online-playlist     1.0     1   0     NaN    ...      \n",
       "\n",
       "   expiration_date_year  expiration_date_month  song_length  genre_ids  \\\n",
       "0                  2017                     10     206471.0        359   \n",
       "1                  2017                      9     284584.0       1259   \n",
       "2                  2017                      9     225396.0       1259   \n",
       "3                  2017                      9     255512.0       1019   \n",
       "4                  2017                     10     187802.0       1011   \n",
       "\n",
       "       artist_name                                 composer  lyricist  \\\n",
       "0         Bastille                     Dan Smith| Mark Crew       NaN   \n",
       "1  Various Artists                                      NaN       NaN   \n",
       "2              Nas     N. Jones、W. Adams、J. Lordan、D. Ingle       NaN   \n",
       "3         Soundway                            Kwadwo Donkoh       NaN   \n",
       "4      Brett Young  Brett Young| Kelly Archer| Justin Ebach       NaN   \n",
       "\n",
       "  language number_of_genres song_year  \n",
       "0     52.0              1.0    2016.0  \n",
       "1     52.0              1.0    1999.0  \n",
       "2     52.0              1.0    2006.0  \n",
       "3     -1.0              1.0    2010.0  \n",
       "4     52.0              1.0    2016.0  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.merge(data,df_members,how='left',on='msno')\n",
    "data = pd.merge(data,df_songs,how='left',on='song_id')\n",
    "data = pd.merge(data,songs_extra,how='left',on='song_id')\n",
    "\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "msno                            0\n",
       "song_id                         0\n",
       "source                          0\n",
       "source_screen_name         577687\n",
       "source_system_tab           23467\n",
       "source_type                 28836\n",
       "target                    2556790\n",
       "city                            0\n",
       "bd                              0\n",
       "gender                    4013703\n",
       "registered_via                  0\n",
       "registration_init_time          0\n",
       "expiration_date                 0\n",
       "use_days                        0\n",
       "expiration_date_year            0\n",
       "expiration_date_month           0\n",
       "song_length                   139\n",
       "genre_ids                  160565\n",
       "artist_name                   139\n",
       "composer                  2295010\n",
       "lyricist                  4403541\n",
       "language                      192\n",
       "number_of_genres              139\n",
       "song_year                  774501\n",
       "dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "data['song_length'] = np.log1p(data['song_length'])\n",
    "data.drop([ 'lyricist','composer'], axis = 1, inplace = True)\n",
    "\n",
    "#把小于3的值去除\n",
    "ulimit=3 \n",
    "data = data[data['bd'] > ulimit]\n",
    "ulimit1=90\n",
    "data = data[data['bd'] < ulimit1]\n",
    "\n",
    "data['language'][10] = -100\n",
    "data['number_of_genres'] = data['genre_ids'].apply(count_vals)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "songs_features1 = [ 'genre_ids','artist_name','song_id','msno']\n",
    "for col in songs_features1:\n",
    "    # 创建一个新列表示播放次数\n",
    "    name = str(col+'_counts')\n",
    "    data[name] = np.ones(len(data),'int64')\n",
    "    \n",
    "    data_temp = data[[col,name]]\n",
    "    # 得到播放/点播次数\n",
    "    count = data_temp.groupby(by = col,as_index = False).count()\n",
    "    #删除原来的次数列\n",
    "    data.drop(name, axis=1, inplace = True)\n",
    "    # 融合入data表\n",
    "    data = pd.merge(data, count, how='left', on=col)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "songs_features2 = ['genre_ids','artist_name']\n",
    "for col in songs_features2:\n",
    "    value_counts_col = data[col].value_counts()\n",
    "    \n",
    "    rare_threshold = 100\n",
    "    value_counts_rare = list(value_counts_col[value_counts_col < rare_threshold ].index)\n",
    "    \n",
    "    #rare_index = data[col].isin(value_counts_rare)\n",
    "    #data.loc[ data[col].isin(value_counts_rare), col] = 'Others'\n",
    "    data[col].fillna('Others',inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "feats_to_encode = ['source_screen_name',\n",
    "       'source_system_tab', 'source_type','city', 'gender',\n",
    "       'registered_via', 'expiration_date_year', 'expiration_date_month',\n",
    "        'genre_ids', 'artist_name',\n",
    "       'language']\n",
    "\n",
    "for col in feats_to_encode:\n",
    "    data[col] = data[col].astype(object)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "data['language'].fillna(0,inplace = True)\n",
    "data['source_screen_name'].fillna('unknown',inplace = True)\n",
    "data[ 'source_system_tab'].fillna('unknown',inplace = True)\n",
    "data[ 'source_type'].fillna('unknown',inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'\\nfor col in feats_to_encode:\\n    data[col] = le.fit_transform(data[col])\\n'"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[ 'gender'].fillna('unknown',inplace = True)\n",
    "from sklearn.preprocessing import LabelEncoder\n",
    "le = LabelEncoder()\n",
    "#data['artist_name'] = le.fit_transform(data['artist_name'])\n",
    "\n",
    "for col in feats_to_encode:\n",
    "    data[col] = le.fit_transform(data[col])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>msno</th>\n",
       "      <th>song_id</th>\n",
       "      <th>source</th>\n",
       "      <th>source_screen_name</th>\n",
       "      <th>source_system_tab</th>\n",
       "      <th>source_type</th>\n",
       "      <th>target</th>\n",
       "      <th>city</th>\n",
       "      <th>bd</th>\n",
       "      <th>gender</th>\n",
       "      <th>...</th>\n",
       "      <th>song_length</th>\n",
       "      <th>genre_ids</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>language</th>\n",
       "      <th>number_of_genres</th>\n",
       "      <th>song_year</th>\n",
       "      <th>genre_ids_counts</th>\n",
       "      <th>artist_name_counts</th>\n",
       "      <th>song_id_counts</th>\n",
       "      <th>msno_counts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=</td>\n",
       "      <td>bhp/MpSNoqoxOIB+/l8WPqu6jldth4DIpCm3ayXnJqM=</td>\n",
       "      <td>train</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>11</td>\n",
       "      <td>24</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>12.558787</td>\n",
       "      <td>22</td>\n",
       "      <td>29245</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>1999.0</td>\n",
       "      <td>151379.0</td>\n",
       "      <td>272202.0</td>\n",
       "      <td>1</td>\n",
       "      <td>730</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=</td>\n",
       "      <td>JNWfrrC7zNN7BdMpsISKa4Mw+xVJYNnxXh3/Epw7QgY=</td>\n",
       "      <td>train</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>11</td>\n",
       "      <td>24</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>12.325619</td>\n",
       "      <td>22</td>\n",
       "      <td>19505</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>2006.0</td>\n",
       "      <td>151379.0</td>\n",
       "      <td>310.0</td>\n",
       "      <td>5</td>\n",
       "      <td>730</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=</td>\n",
       "      <td>2A87tzfnJTSWqD7gIZHisolhe4DMdzkbd6LzO1KHjNs=</td>\n",
       "      <td>train</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>11</td>\n",
       "      <td>24</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>12.451029</td>\n",
       "      <td>171</td>\n",
       "      <td>25096</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>730</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=</td>\n",
       "      <td>VkILU0H1h3NMmk9MQrXouNudGk5n8Ls5cqRRuBxeTh4=</td>\n",
       "      <td>train</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>11</td>\n",
       "      <td>24</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>12.345761</td>\n",
       "      <td>98</td>\n",
       "      <td>2690</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>2014.0</td>\n",
       "      <td>2994745.0</td>\n",
       "      <td>6238.0</td>\n",
       "      <td>2747</td>\n",
       "      <td>730</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>uHqAtShXTRXju5GE8ri3ITsVFepPf8jUoCF7ffNOuqE=</td>\n",
       "      <td>/bU6IRSK+YNlNbaTkxo7bhsb2EDLPrnksdX3ggcZNhI=</td>\n",
       "      <td>train</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>13</td>\n",
       "      <td>26</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>12.538842</td>\n",
       "      <td>48</td>\n",
       "      <td>20446</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>122740.0</td>\n",
       "      <td>7179.0</td>\n",
       "      <td>28</td>\n",
       "      <td>841</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           msno  \\\n",
       "0  Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=   \n",
       "1  Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=   \n",
       "2  Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=   \n",
       "3  Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=   \n",
       "4  uHqAtShXTRXju5GE8ri3ITsVFepPf8jUoCF7ffNOuqE=   \n",
       "\n",
       "                                        song_id source  source_screen_name  \\\n",
       "0  bhp/MpSNoqoxOIB+/l8WPqu6jldth4DIpCm3ayXnJqM=  train                   8   \n",
       "1  JNWfrrC7zNN7BdMpsISKa4Mw+xVJYNnxXh3/Epw7QgY=  train                   8   \n",
       "2  2A87tzfnJTSWqD7gIZHisolhe4DMdzkbd6LzO1KHjNs=  train                   8   \n",
       "3  VkILU0H1h3NMmk9MQrXouNudGk5n8Ls5cqRRuBxeTh4=  train                   8   \n",
       "4  /bU6IRSK+YNlNbaTkxo7bhsb2EDLPrnksdX3ggcZNhI=  train                   8   \n",
       "\n",
       "   source_system_tab  source_type  target  city  bd  gender     ...       \\\n",
       "0                  3            4     1.0    11  24       0     ...        \n",
       "1                  3            4     1.0    11  24       0     ...        \n",
       "2                  3            4     1.0    11  24       0     ...        \n",
       "3                  3            4     1.0    11  24       0     ...        \n",
       "4                  3            3     1.0    13  26       1     ...        \n",
       "\n",
       "   song_length  genre_ids  artist_name  language  number_of_genres  song_year  \\\n",
       "0    12.558787         22        29245        10                 1     1999.0   \n",
       "1    12.325619         22        19505        10                 1     2006.0   \n",
       "2    12.451029        171        25096         1                 1     2010.0   \n",
       "3    12.345761         98         2690         7                 1     2014.0   \n",
       "4    12.538842         48        20446        10                 1     2007.0   \n",
       "\n",
       "   genre_ids_counts  artist_name_counts  song_id_counts  msno_counts  \n",
       "0          151379.0            272202.0               1          730  \n",
       "1          151379.0               310.0               5          730  \n",
       "2              64.0                 1.0               1          730  \n",
       "3         2994745.0              6238.0            2747          730  \n",
       "4          122740.0              7179.0              28          841  \n",
       "\n",
       "[5 rows x 26 columns]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:4: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  after removing the cwd from sys.path.\n",
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:5: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \"\"\"\n"
     ]
    }
   ],
   "source": [
    "\n",
    "train = data.loc[data['source']=='train']\n",
    "test = data.loc[data['source']=='test']\n",
    "train.drop(['source'],axis=1,inplace=True)\n",
    "test.drop(['source','target'],axis=1,inplace=True)\n",
    "\n",
    "train.to_csv('./data/FE_train_xgboost.csv',index = False)\n",
    "test.to_csv('./data/FE_test_xgboost.csv', index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
